# coding:utf-8
from sqlalchemy import Column, String, Integer, Enum, and_, ForeignKey, Float
from sqlalchemy.orm import relationship

from database import dbconnect, session_maker, model_list

_, _, DBase = dbconnect()


class WeeklyReport(DBase):
    __tablename__ = 'weekly_report'
    __table_args__ = {'extend_existing': True}
    id = Column(Integer, primary_key=True)
    # cluster_id = Column(Integer)
    cluster_id = Column(Integer, ForeignKey('cluster.id'))  # 本表的cluster_id字段具体外键关联到cluster的那个字段
    cluster = relationship("Cluster")  # cluster对象，定义其关联到Cluster表对象
    year = Column(Integer)
    week = Column(Integer)
    # date_span = Column(String(255))
    scope = Column(Enum('node', 'cluster', 'web'))
    node = Column(String(255))
    d_cpu = Column(Float)
    d_mem = Column(Float)
    d_disk = Column(Float)
    d_net_in = Column(Float)
    d_net_out = Column(Float)
    d_aval = Column(Float)

    # 查询所有
    def find_all(self):
        with session_maker() as session:
            # result = session.query(WeeklyReport).order_by(WeeklyReport.year.asc(), WeeklyReport.week.asc()).all()
            result = session.query(WeeklyReport).all()
            list = model_list(result)
            return list

    # 查询所有
    def find_by_week(self, year, week):
        with session_maker() as session:
            result = session.query(WeeklyReport).filter(and_(
                WeeklyReport.year == year,
                WeeklyReport.week == week
            )).all()
            list = model_list(result)
            return list

    def find_distinct_week(self):
        with session_maker() as session:
            result = session.query(WeeklyReport.year, WeeklyReport.week).distinct(
                WeeklyReport.year, WeeklyReport.week).order_by(
                WeeklyReport.year.asc(), WeeklyReport.week.asc()).all()
            print(result)
            list = []
            for i in result:
                list.append("{}-{}".format(i[0], i[1]))
            return list

    def insert(self, cluster_id, year, week, scope, node, d_cpu, d_mem, d_disk, d_net_in, d_net_out):
        with session_maker() as session:
            new_line = WeeklyReport(
                cluster_id=cluster_id, year=year, week=week, scope=scope, node=node, d_cpu=d_cpu, d_mem=d_mem,
                d_disk=d_disk, d_net_in=d_net_in, d_net_out=d_net_out
            )
            session.add(new_line)

    def insert_web(self, cluster_id, year, week, scope, web, d_aval):
        with session_maker() as session:
            new_line = WeeklyReport(
                cluster_id=cluster_id, year=year, week=week, scope=scope, node=web, d_aval=d_aval
            )
            session.add(new_line)


class WeeklyReportService(DBase):
    __tablename__ = 'weekly_report_service'
    id = Column(Integer, primary_key=True)
    cluster_id = Column(Integer, ForeignKey('cluster.id'))  # 本表的cluster_id字段具体外键关联到cluster的那个字段
    cluster = relationship("Cluster")  # cluster对象，定义其关联到Cluster表对象
    year = Column(Integer)
    week = Column(Integer)

    service = Column(String(255))
    d_apdex = Column(Float)
    d_resp_time = Column(Integer)
    d_sla = Column(Float)
    d_cpm = Column(Integer)

    # 查询所有
    def find_all(self):
        with session_maker() as session:
            result = session.query(WeeklyReportService).all()
            list = model_list(result)
            return list


    def insert(self, cluster_id, year, week, service, d_apdex, d_resp_time, d_sla, d_cpm):
        with session_maker() as session:
            new_line = WeeklyReportService(
                cluster_id=cluster_id, year=year, week=week, service=service, d_apdex=d_apdex, d_resp_time=d_resp_time,
                d_sla=d_sla, d_cpm=d_cpm
            )
            session.add(new_line)
